package internal

import (
	"context"
	"database/sql"
	"fmt"
	"regexp"
	"time"

	_ "github.com/go-sql-driver/mysql" // mysql driver
)

// MyDb db struct
type MyDb struct {
	Db          *sql.DB
	dbType      string
	DbName      string
	config      *Config
	maxRetries  int
	retryDelay  time.Duration
}

// NewMyDb parse dsn
func NewMyDb(dsn string, dbname string, dsnName string, ssh string, config *Config) (*MyDb, error) {
	Debug("Creating database connection for %s to database %s", dsnName, dbname)
	
	// 匹配字符串
	re := regexp.MustCompile(`^([^:]+):([^@]+)@([^:]+):([^/]+)$`)
	matches := re.FindStringSubmatch(dsn)
	if len(matches) != 5 {
		return nil, NewDatabaseError(fmt.Sprintf("invalid DSN format: %s", dsn), nil)
	}

	var finalDSN string
	if len(ssh) > 0 {
		if err := MysqlUseSsh(dsnName, ssh); err != nil {
			return nil, NewSSHError("failed to setup SSH connection", err)
		}
		finalDSN = fmt.Sprintf("%s:%s@%s(%s:%s)/%s?parseTime=true&timeout=%v&readTimeout=%v&writeTimeout=%v", 
			matches[1], matches[2], dsnName, matches[3], matches[4], dbname,
			config.ConnectTimeout.ToDuration(), config.QueryTimeout.ToDuration(), config.QueryTimeout.ToDuration())
	} else {
		finalDSN = fmt.Sprintf("%s:%s@(%s:%s)/%s?parseTime=true&timeout=%v&readTimeout=%v&writeTimeout=%v", 
			matches[1], matches[2], matches[3], matches[4], dbname,
			config.ConnectTimeout.ToDuration(), config.QueryTimeout.ToDuration(), config.QueryTimeout.ToDuration())
	}

	db, err := sql.Open("mysql", finalDSN)
	if err != nil {
		return nil, NewDatabaseError(fmt.Sprintf("failed to connect to database %s", dbname), err)
	}

	// 设置连接池参数
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	// 测试连接
	ctx, cancel := context.WithTimeout(context.Background(), config.ConnectTimeout.ToDuration())
	defer cancel()
	
	if err := db.PingContext(ctx); err != nil {
		db.Close()
		return nil, NewDatabaseError(fmt.Sprintf("failed to ping database %s", dbname), err)
	}

	Info("Successfully connected to database %s", dbname)
	
	return &MyDb{
		Db:         db,
		dbType:     dsnName,
		DbName:     dbname,
		config:     config,
		maxRetries: config.MaxRetries,
		retryDelay: 1 * time.Second,
	}, nil
}

// Close closes the database connection
func (db *MyDb) Close() error {
	if db.Db != nil {
		return db.Db.Close()
	}
	return nil
}

// withRetry 带重试机制执行函数
func (db *MyDb) withRetry(operation func() error) error {
	var lastErr error
	for i := 0; i <= db.maxRetries; i++ {
		if i > 0 {
			Debug("Retrying operation, attempt %d/%d", i+1, db.maxRetries+1)
			time.Sleep(db.retryDelay * time.Duration(i))
		}
		
		if err := operation(); err != nil {
			lastErr = err
			Warn("Operation failed (attempt %d/%d): %v", i+1, db.maxRetries+1, err)
			continue
		}
		
		return nil
	}
	
	return NewDatabaseError("operation failed after retries", lastErr)
}

// GetTableNames table names
func (db *MyDb) GetTableNames() ([]string, error) {
	var tables []string
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		rs, err := db.Db.QueryContext(ctx, "SHOW TABLE STATUS")
		if err != nil {
			return err
		}
		defer rs.Close()

		tables = tables[:0] // 清空slice
		columns, _ := rs.Columns()
		for rs.Next() {
			var values = make([]interface{}, len(columns))
			valuePtrs := make([]interface{}, len(columns))
			for i := range columns {
				valuePtrs[i] = &values[i]
			}
			if err := rs.Scan(valuePtrs...); err != nil {
				return NewDatabaseError("failed to scan table status", err)
			}
			var valObj = make(map[string]interface{})
			for i, col := range columns {
				var v interface{}
				val := values[i]
				b, ok := val.([]byte)
				if ok {
					v = string(b)
				} else {
					v = val
				}
				valObj[col] = v
			}
			if valObj["Engine"] != nil {
				tables = append(tables, valObj["Name"].(string))
			}
		}
		return nil
	})
	
	if err != nil {
		return nil, err
	}
	
	Debug("Found %d tables in database %s", len(tables), db.DbName)
	return tables, nil
}

// GetProcedureNames Get procedure names
func (db *MyDb) GetProcedureNames() ([]string, error) {
	var procedures []string
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		rs, err := db.Db.QueryContext(ctx, `SELECT SPECIFIC_NAME
			FROM information_schema.ROUTINES
			WHERE ROUTINE_TYPE = 'PROCEDURE' 
			AND ROUTINE_SCHEMA = DATABASE()`)
		if err != nil {
			return err
		}
		defer rs.Close()

		procedures = procedures[:0] // 清空slice
		for rs.Next() {
			var vname string
			if err := rs.Scan(&vname); err != nil {
				return NewDatabaseError("failed to scan procedure name", err)
			}
			procedures = append(procedures, vname)
		}
		return nil
	})
	
	if err != nil {
		return nil, err
	}
	
	Debug("Found %d procedures in database %s", len(procedures), db.DbName)
	return procedures, nil
}

// GetTableSchema table schema
func (db *MyDb) GetTableSchema(name string) (string, error) {
	var schema string
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		rs, err := db.Db.QueryContext(ctx, fmt.Sprintf("SHOW CREATE TABLE `%s`", name))
		if err != nil {
			// 表可能不存在
			return err
		}
		defer rs.Close()

		for rs.Next() {
			var vname string
			if err := rs.Scan(&vname, &schema); err != nil {
				return NewDatabaseError(fmt.Sprintf("failed to get schema for table %s", name), err)
			}
			// 生成建表语句中可能包含了字段使用的字符集，可能在其他库中不显示使用的字符集而对比出差异。需要替换掉 CHARACTER SET xxxx
			reg, _ := regexp.Compile("CHARACTER SET [a-z0-9_]+ ")
			schema = reg.ReplaceAllString(schema, "")
		}
		return nil
	})
	
	if err != nil {
		Debug("Failed to get schema for table %s: %v", name, err)
		return "", nil // 表不存在时返回空schema
	}
	
	return schema, nil
}

// GetProcedureSchema Get procedure schema
func (db *MyDb) GetProcedureSchema(name string) (string, error) {
	var schema string
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		rs, err := db.Db.QueryContext(ctx, fmt.Sprintf("SHOW CREATE PROCEDURE `%s`", name))
		if err != nil {
			return err
		}
		defer rs.Close()
		
		for rs.Next() {
			var vname, sqlmode, chars, coll, dbcoll string
			if err := rs.Scan(&vname, &sqlmode, &schema, &chars, &coll, &dbcoll); err != nil {
				return NewDatabaseError(fmt.Sprintf("failed to get procedure schema for %s", name), err)
			}
		}
		return nil
	})
	
	if err != nil {
		Debug("Failed to get procedure schema for %s: %v", name, err)
		return "", nil
	}
	
	return schema, nil
}

// Query execute sql query
func (db *MyDb) Query(query string, args ...interface{}) (*sql.Rows, error) {
	return db.QueryContext(context.Background(), query, args...)
}

// QueryContext execute sql query with context
func (db *MyDb) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	Debug("Executing query on %s: %s", db.dbType, query)
	
	var rows *sql.Rows
	err := db.withRetry(func() error {
		var err error
		rows, err = db.Db.QueryContext(ctx, query, args...)
		return err
	})
	
	if err != nil {
		return nil, NewDatabaseError("query execution failed", err)
	}
	
	return rows, nil
}

// GetTableFields 获取表字段信息
func (db *MyDb) GetTableFields(tableName string) (map[string]string, error) {
	var fields = make(map[string]string)
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		query := fmt.Sprintf("DESCRIBE `%s`", tableName)
		rs, err := db.Db.QueryContext(ctx, query)
		if err != nil {
			return err
		}
		defer rs.Close()
		
		fields = make(map[string]string) // 清空map
		
		for rs.Next() {
			var field, fieldType, null, key, defaultValue, extra string
			if err := rs.Scan(&field, &fieldType, &null, &key, &defaultValue, &extra); err != nil {
				return NewDatabaseError("failed to scan field info", err)
			}
			fields[field] = fieldType
		}
		return nil
	})
	
	return fields, err
}

// Exec 执行SQL语句
func (db *MyDb) Exec(query string, args ...interface{}) (sql.Result, error) {
	var result sql.Result
	
	err := db.withRetry(func() error {
		ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
		defer cancel()
		
		var err error
		result, err = db.Db.ExecContext(ctx, query, args...)
		return err
	})
	
	return result, err
}

// Prepare 准备SQL语句
func (db *MyDb) Prepare(query string) (*sql.Stmt, error) {
	ctx, cancel := context.WithTimeout(context.Background(), db.config.QueryTimeout.ToDuration())
	defer cancel()
	
	return db.Db.PrepareContext(ctx, query)
}
